package data.dao.daoImplementation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import data.dao.*;
import data.dto.*;
import data.searchdto.*;

public class LeaveDetailCommentDAOImpl implements LeaveDetailCommentDAO {

	@Override
	public ArrayList<LeaveDetailCommentDTO> findLeaveDetailComment(
			LeaveDetailSearchDTO Leavedetailsearchdto)
			throws LeaveDAOException, java.sql.SQLException {

		String sql = "SELECT * FROM leavedetailcomment WHERE (id = ? ) ";
		PreparedStatement stmt = null;
		ArrayList<LeaveDetailCommentDTO> searchResults = null;
		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			searchResults = searchMatching(util.Database.getInstance()
					.getConnection(), Leavedetailsearchdto);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				stmt.close();
		}
		return searchResults;
	}

	@Override
	public ArrayList<LeaveDetailCommentDTO> findAllLeaveDetailComment()
			throws LeaveDAOException, SQLException, java.sql.SQLException {
		String sql = "SELECT * FROM leavedetailcomment ORDER BY id ASC ";
		ArrayList<LeaveDetailCommentDTO> searchResults = listQuery(util.Database
				.getInstance().getConnection().prepareStatement(sql));

		return searchResults;
	}

	@Override
	public void insertLeaveDetailComment(
			LeaveDetailCommentDTO leaveDetailComment) throws SQLException,
			java.sql.SQLException {

		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO leavedetailcomment ( id, leaveid, comment, "
					+ "isActive) VALUES (?, ?, ?, 1) ";
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			stmt.setInt(1, leaveDetailComment.getId());
			stmt.setInt(2, leaveDetailComment.getLeaveID());
			stmt.setString(3, leaveDetailComment.getComment());

			int rowcount = stmt.executeUpdate();
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	@Override
	public void updateLeaveDetailComment(
			LeaveDetailCommentDTO leaveDetailComment) throws NotFoundException,
			SQLException, java.sql.SQLException {

		String sql = "UPDATE leavedetailcomment SET leaveid = ?, comment = ?, isActive = 1 WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, leaveDetailComment.getLeaveID());
			stmt.setString(2, leaveDetailComment.getComment());

			stmt.setInt(3, leaveDetailComment.getId());

			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void deleteLeaveDetailComment(
			LeaveDetailCommentDTO leaveDetailComment) throws LeaveDAOException,
			java.sql.SQLException, NotFoundException, SQLException {

		String sql = "DELETE FROM leavedetailcomment WHERE id = ? ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, leaveDetailComment.getId());

			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	protected ArrayList<LeaveDetailCommentDTO> listQuery(PreparedStatement stmt)
			throws SQLException, java.sql.SQLException {

		ArrayList<LeaveDetailCommentDTO> searchResults = new ArrayList<LeaveDetailCommentDTO>();
		ResultSet result = null;

		try {
			result = stmt.executeQuery();

			while (result.next()) {
				LeaveDetailCommentDTO temp = new LeaveDetailCommentDTO();

				temp.setId(result.getInt("id"));
				temp.setLeaveID(result.getInt("leaveid"));
				temp.setComment(result.getString("comment"));
				searchResults.add(temp);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		}

		return searchResults;
	}

	public ArrayList<LeaveDetailCommentDTO> searchMatching(Connection conn,
			LeaveDetailSearchDTO valueObject) throws SQLException,
			java.sql.SQLException {

		ArrayList<LeaveDetailCommentDTO> searchResults;

		boolean first = true;
		StringBuffer sql = new StringBuffer(
				"SELECT * FROM leavedetailcomment WHERE 1=1 ");

		if (valueObject.getLeaveID() != 0) {
			if (first) {
				first = false;
			}
			sql.append("AND leaveid = ").append(valueObject.getLeaveID())
					.append(" ");
		}

		sql.append("ORDER BY id ASC ");

		// Prevent accidential full table results.
		// Use loadAll if all rows must be returned.
		if (first)
			searchResults = new ArrayList<LeaveDetailCommentDTO>();
		else
			searchResults = listQuery(conn.prepareStatement(sql.toString()));

		return searchResults;
	}

}
